package cn.com.libertymutual.sp.dao;

import java.util.List;

import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.PagingAndSortingRepository;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;

import cn.com.libertymutual.sp.bean.TbSpStoreProduct;

@Repository
public interface StoreProductDao extends PagingAndSortingRepository<TbSpStoreProduct, Integer>, JpaSpecificationExecutor<TbSpStoreProduct> {

	@Query("from TbSpStoreProduct where userCode =?1 and isShow=1 order by serialNo desc")
	List<TbSpStoreProduct> findUserCode(String userCode);

	@Query("select st.id,st.productId,st.riskSerialNo,st.serialNo from TbSpStoreProduct st,TbSpProduct pro where st.productId = pro.id and pro.riskCode=?1 and st.userCode = ?2 and st.isShow='1' order by st.riskSerialNo desc")
	List<Object[]> findAndOrderProduct(String riskCode, String userCode);

	@Query("select max(serialNo) from TbSpStoreProduct where userCode =?1 ")
	Integer findMaxSerialNoByUser(String userCode);

	@Transactional
	@Modifying
	@Query("delete from TbSpStoreProduct  where productId = ?1")
	void deleteByProId(Integer productId);

	@Transactional
	@Modifying
	@Query("delete from TbSpStoreProduct  where productId = ?1 and userCode= ?2")
	void deleteByProId(Integer productId, String userCode);

	@Query("from TbSpStoreProduct where userCode =?1 and productId=?2")
	TbSpStoreProduct findByUCodeAndPId(String userCode, Integer productId);

	// 清除下级渠道的产品
	@Transactional
	@Modifying
	@Query("delete from TbSpStoreProduct  where userCode = ?1")
	void deleteByUserCode(String userCode);
	
	@Transactional
	@Modifying
	@Query(value="insert into tb_sp_storeproduct (USER_CODE,Product_id,RISK_SERIAL_NO,SERIAL_NO,IS_SHOW) select u.USER_CODE,?1,(IFNULL((select MAX(SERIAL_NO) from tb_sp_storeproduct p where p.USER_CODE = u.USER_CODE)+1,1)), (IFNULL((select MAX(SERIAL_NO) from tb_sp_storeproduct p where p.USER_CODE = u.USER_CODE)+1,1)),1 FROM tb_sp_user u where u.STORE_FLAG='1' and u.REGISTER_TYPE='0'",nativeQuery=true)
	void insertAutoMatic(Integer productId);


}
